﻿/*
 * File nay se thuc thi giao dien cua interface IBookTicketRepository
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using QLMB.ViewModels;

namespace QLMB.Models
{
    public class BookTicketRepository : IBookTicketRepository
    {
        private BookingEntities db = new BookingEntities();
        public BookingEntities DB
        {
            get { return db; }
            set { db = value; }
        }

        public void CommitChanges()
        {
            DB.SaveChanges();
        }

        #region BookTicket
        public IDictionary<string, string> LayDSSanbaydi()
        {
            var DSSB = (from p in db.DUONGBAYs
                        where ((p.HangKhaiThac.CompareTo("AIRLINE")) == 0) && p.KichHoat == true
                       select new
                       {
                           SanBayID = p.SanBayDi,
                           ThanhPho = p.SANBAY.THANHPHO.TenTP
                       }).Distinct();
            IDictionary<string, string> result = new Dictionary<string, string>();
            foreach (var SB in DSSB)
            {
                result.Add(SB.SanBayID,SB.ThanhPho);
            }
            return result;
        }
        public string LayMaDuongBay(string NoiDi, string NoiDen)
        {
            return (from p in db.DUONGBAYs
                    where (p.SanBayDi == NoiDi && p.SanBayDen == NoiDen)
                    select p).First().MaDuongBay;
        }
        public IEnumerable<CHUYENBAY> LayDSChuyenBay(DateTime Ngaydi, string Sanbaydi, string Sanbayden)
        {  
            Ngaydi = Ngaydi.Date;
            DateTime nextday = Ngaydi.AddDays(1);
            string mdb = LayMaDuongBay(Sanbaydi, Sanbayden);
            
            IEnumerable<CHUYENBAY> DSCB = from p in db.CHUYENBAYs
                                          where (p.NgayDi.Value >= Ngaydi && p.NgayDi.Value < nextday && p.MaDuongBay == mdb
                                                && p.CT_GHECHUYENBAY.Sum(o => o.SoGheDaDat) < p.MAYBAY.TongSoGhe && p.TrangThaiCB.CompareTo("Đặt vé") != 0)
                                          orderby p.DUONGBAY.GiaCoBan ascending
                                          select p;
            return DSCB;
        }
        public Decimal TimGiaThapNhatTrongNgay(DateTime Ngaybay, string Sanbaydi, string Sanbayden)
        {
            IEnumerable<CHUYENBAY> temp = LayDSChuyenBay(Ngaybay, Sanbaydi, Sanbayden);
            int c = temp.Count();
            if (c != 0)
                return (Decimal)(temp.First().DUONGBAY.GiaCoBan - temp.First().DUONGBAY.GiaCoBan*temp.First().KhuyenMai/100);
            else
                return -1;
        }
        public CHUYENBAY TimCBGiaThapNhat(DateTime Ngaybay, string Sanbaydi, string Sanbayden)
        {
            IEnumerable<CHUYENBAY> temp = LayDSChuyenBay(Ngaybay, Sanbaydi, Sanbayden);
            int c = temp.Count();
            if (c != 0)
                return temp.First();
            else
                return null;
        }
        public IEnumerable<LOAIDICHVU> LayDSCacDichVu(string MaCB, int soluongdatve)
        {
            IEnumerable<string> maghe = (from q in db.CHUYENBAYs
                                         where (q.MaCB == MaCB)
                                         select q).First().CT_GHECHUYENBAY.Where(o => o.SoGheDaDat + soluongdatve <= o.GHEMAYBAY.SoLuong).Select(o => o.GHEMAYBAY.MaLoaiGhe);

            return from p in db.LOAIDICHVUs
                   where (maghe.Contains(p.MaLoaiGhe))
                   select p;
        }
        public IEnumerable<LOAIDICHVU> LayDSCacDichVu()
        {
            return from p in db.LOAIDICHVUs
                   select p;
        }
        public CHUYENBAY LayChuyenBay(string MaCB)
        {
            return (from p in db.CHUYENBAYs
                    where (p.MaCB == MaCB)
                    select p).First();
        }
        public LOAIDICHVU LayLoaiDichVu(string MaLDV)
        {
            return (from p in db.LOAIDICHVUs
                    where (p.MaLoaiDV == MaLDV)
                    select p).First();
        }
        private List<string> TaoMaGheNgoi(string macb, string madv, int soluong, List<string> DSGhe)
        {
            int i = 0;
            int id = 0;
            int temp = -1;
            int sldd = DSGhe.Count;
            string maloaighe = db.LOAIDICHVUs.Where(o => o.MaLoaiDV == madv).First().MaLoaiGhe.ToString().Trim();
            string mamaybay = db.CHUYENBAYs.Where(o => o.MaCB == macb).Select(r => r.MAYBAY.MaMB).First();
            int tong = int.Parse(db.GHEMAYBAYs.Where(o => o.MaMB == mamaybay && o.MaLoaiGhe == maloaighe).First().SoLuong.ToString());
            int len = maloaighe.Length;
            List<string> dsghe = new List<string>();
            if ((DSGhe.Count + soluong) >= tong)
                return null;
            for (i = 0; i < tong; i++)
            {
                if(id != sldd)
                    temp = int.Parse(DSGhe[id].Substring(len));
                else
                    temp = tong;
                if (i != temp)
                {
                    if (soluong <= temp - i)
                    {
                        for (int j = 0; j < soluong; j++)
                        {
                            dsghe.Add(maloaighe + i.ToString());
                            i++;
                        }
                        return dsghe;
                    }
                    else
                    {
                        i = int.Parse(DSGhe[id]);
                    }
                }
                else
                {
                    id++;
                }
            }
            dsghe.AddRange(TaoMaGheNgoi(macb, madv, tong, DSGhe));
            dsghe.AddRange(TaoMaGheNgoi(macb, madv, tong, DSGhe));
            return dsghe;
        }
        public HANHKHACH LayHanhKhach(string cmnd)
        {
            int t = db.HANHKHACHes.Where(r => r.CMND == cmnd).Count();
            if (t != 0)
                return db.HANHKHACHes.Where(r => r.CMND == cmnd).First();
            return null;
        }
        public HANHKHACHTHANTHIET LayHKTT(string name, string pass)
        {
            int t = db.HANHKHACHTHANTHIETs.Where(r => r.HANHKHACH.CMND ==name && r.MatKhau == pass).Count();
            if (t != 0)
                return db.HANHKHACHTHANTHIETs.Where(r => r.HANHKHACH.CMND == name && r.MatKhau == pass).First();
            return null;
        }   
        public bool KiemtraHK(string cmnd, string macb)
        {
            IEnumerable<string> ds_cmnd = (from p in db.CT_VECHUYENBAY
                                           where (p.MaCB == macb)
                                           select p.HANHKHACH.CMND);
            return (ds_cmnd.Contains(cmnd));
            
        }
        public List<SANBAY> LayHanhTrinh(string macb)
        {
            List<SANBAY> result = new List<SANBAY>();
            CHUYENBAY cb = db.CHUYENBAYs.Single(x => x.MaCB == macb);
            string madb = cb.DUONGBAY.MaDuongBay;
            if (cb.DUONGBAY.HanhTrinh == "Hợp Tác")
            {
                IEnumerable<string> ds_madbnt = db.CT_DUONGBAY.Where(x => x.MaDuongBayChinh == madb).OrderBy(x=>x.STT).Select(x => x.MaDuongBayNoiTiep);
                foreach (string mdb in ds_madbnt)
                    result.Add(db.DUONGBAYs.Single(x => x.MaDuongBay == mdb).SANBAY);
                result.Add(cb.DUONGBAY.SANBAY1);
                return result;
            }
            else
            {
                result.Add(cb.DUONGBAY.SANBAY);
                result.Add(cb.DUONGBAY.SANBAY1);
                return result;
            }


        }
        public string DatVe(InfoFlight info)
        {
            #region Tao du lieu
            VECHUYENBAY vechuyenbay = new VECHUYENBAY();
            vechuyenbay.NgayDatVe = DateTime.Now;
            vechuyenbay.LoaiVe = info.Loaive;
            vechuyenbay.TongTien = info.TongTien;
            vechuyenbay.MaTrangThaiVe = "002";
            vechuyenbay.SoLuong = info.SoNguoiLon + info.SoTreEm;
            int sl_cb = 1;
            if (info.Loaive == "Khu hoi")
                sl_cb = 2;
            if(db.VECHUYENBAYs.Count() == 0)
            {
                vechuyenbay.MaVe = "VCB1";
            }
            else
            {
                vechuyenbay.MaVe = "VCB" + (int.Parse(db.VECHUYENBAYs.AsEnumerable<VECHUYENBAY>().Last().MaVe.ToString().Substring(3)) + 1).ToString();
            }
            int sl_hk = info.dsHK.Count;
            int id_ctvcb = 0;
            int id_hk = 0;
            int id_cnd = 0;
            int id_cnv = 0;
            int c = 0;
            List<HANHKHACH> dshk = new List<HANHKHACH>(sl_hk);
            List<CT_VECHUYENBAY> ds_ctvcb = new List<CT_VECHUYENBAY>(sl_cb * sl_hk);
            HANHKHACH hkdd = new HANHKHACH();
            List<string> dscngoi1 = db.CT_VECHUYENBAY.Where(p => p.MaCB == info.MaChuyenBayDi && p.MaLoaiDV == info.Loaidichvudi).OrderBy(p=>p.ChoNgoi).Select(p => p.ChoNgoi).ToList();
            List<string> dscngoiphatsinhdi = TaoMaGheNgoi(info.MaChuyenBayDi, info.Loaidichvudi, info.SoTreEm + info.SoNguoiLon, dscngoi1);
            List<string> dscngoiphatsinhve = new List<string>();

            if(sl_cb == 2)
            {
                List<string> dscngoi2 = db.CT_VECHUYENBAY.Where(p => p.MaCB == info.MaChuyenBayVe && p.MaLoaiDV == info.Loaidichvuve).Select(p => p.ChoNgoi).ToList();
                dscngoiphatsinhve = TaoMaGheNgoi(info.MaChuyenBayVe, info.Loaidichvuve, info.SoTreEm + info.SoNguoiLon, dscngoi2);
            }
            #endregion

            #region Them hanh khach va chi tiet ma chuyen bay
            string mahk = db.HANHKHACHes.AsEnumerable<HANHKHACH>().Last().MaHK.ToString().Substring(2);
            c = int.Parse(mahk);
            for (int i = 0; i < sl_hk; i++)
            {
                //Neu trong CSDL da co du lieu hanh khach
                if(db.HANHKHACHes.Count() != 0)
                {
                    //Neu hanh khach khong co trong CSDL
                    if(info.dsHK[i].MaHK == null)
                    {
                        c++;
                        dshk.Add(new HANHKHACH());
                        dshk[id_hk].MaHK = "HK" + c.ToString();
                        dshk[id_hk].Ho = info.dsHK[i].Ho;
                        dshk[id_hk].Ten = info.dsHK[i].Ten;
                        dshk[id_hk].SoDTDiDong = info.dsHK[i].SoDT.ToString();
                        dshk[id_hk].CMND = info.dsHK[i].SoCMND.ToString();
                        if (info.khdd == i.ToString())
                        {
                            info.Nguoidat.MaHK = dshk[id_hk].MaHK;
                        }
                        info.dsHK[i].MaHK = dshk[id_hk].MaHK;
                        db.AddToHANHKHACHes(dshk[id_hk]);
                        id_hk++;
                    }
                    else
                    {
                        if(!db.HANHKHACHTHANTHIETs.Select(x=>x.MaHK).Contains(info.dsHK[i].MaHK))
                        {
                            string tm = info.dsHK[i].MaHK;
                            HANHKHACH temp = db.HANHKHACHes.Single(x => x.MaHK == tm);
                            temp.Ho = info.dsHK[i].Ho;
                            temp.Ten = info.dsHK[i].Ten;
                            temp.SoDTDiDong = info.dsHK[i].SoDT.ToString();
                            temp.CMND = info.dsHK[i].SoCMND.ToString();
                            info.dsHK[i].MaHK = temp.MaHK;
                        }
                    }
                    ds_ctvcb.Add(new CT_VECHUYENBAY());
                    ds_ctvcb[id_ctvcb].MaCTVe = vechuyenbay.MaVe.Substring(2) + info.MaChuyenBayDi.Substring(2) + info.dsHK[i].MaHK.Substring(2);
                    ds_ctvcb[id_ctvcb].MaVe = vechuyenbay.MaVe;
                    ds_ctvcb[id_ctvcb].MaCB = info.MaChuyenBayDi;
                    ds_ctvcb[id_ctvcb].MaHK = info.dsHK[i].MaHK;
                    ds_ctvcb[id_ctvcb].MaLoaiDV = info.Loaidichvudi;
                    ds_ctvcb[id_ctvcb].ChoNgoi = dscngoiphatsinhdi[id_cnd];
                    id_cnd++;
                    id_ctvcb++;
                    if (sl_cb == 2)
                    {
                        ds_ctvcb.Add(new CT_VECHUYENBAY());
                        ds_ctvcb[id_ctvcb].MaCTVe = vechuyenbay.MaVe.Substring(2) + info.MaChuyenBayVe.Substring(2) + info.dsHK[i].MaHK.Substring(2);
                        ds_ctvcb[id_ctvcb].MaVe = vechuyenbay.MaVe;
                        ds_ctvcb[id_ctvcb].MaCB = info.MaChuyenBayVe;
                        ds_ctvcb[id_ctvcb].MaHK = info.dsHK[i].MaHK;
                        ds_ctvcb[id_ctvcb].MaLoaiDV = info.Loaidichvuve;
                        ds_ctvcb[id_ctvcb].ChoNgoi = dscngoiphatsinhve[id_cnv];
                        id_cnv++;
                        id_ctvcb++;
                    }
                    
                }
                else
                {
                    //Neu hanh khach khong co trong CSDL
                    if (info.dsHK[i].MaHK == null)
                    {
                        dshk.Add(new HANHKHACH());
                        dshk[id_hk].MaHK = "HK1";
                        dshk[id_hk].Ho = info.dsHK[i].Ho;
                        dshk[id_hk].Ten = info.dsHK[i].Ten;
                        dshk[id_hk].SoDTDiDong = info.dsHK[i].SoDT.ToString();
                        dshk[id_hk].CMND = info.dsHK[i].SoCMND.ToString();
                        if (info.khdd == i.ToString())
                        {
                            info.Nguoidat.MaHK = dshk[id_hk].MaHK;

                        }
                        info.dsHK[i].MaHK = dshk[id_hk].MaHK;
                        db.AddToHANHKHACHes(dshk[id_hk]);
                        id_hk++;
                    }
                    ds_ctvcb.Add(new CT_VECHUYENBAY());
                    ds_ctvcb[id_ctvcb].MaCTVe = "CTV" + vechuyenbay.MaVe.Substring(3) + info.MaChuyenBayDi.Substring(2) + info.dsHK[i].MaHK.Substring(2);
                    ds_ctvcb[id_ctvcb].MaVe = vechuyenbay.MaVe;
                    ds_ctvcb[id_ctvcb].MaCB = info.MaChuyenBayDi;
                    ds_ctvcb[id_ctvcb].MaHK = info.dsHK[i].MaHK;
                    ds_ctvcb[id_ctvcb].MaLoaiDV = info.Loaidichvudi;
                    ds_ctvcb[id_ctvcb].ChoNgoi = dscngoiphatsinhdi[id_cnd];
                    id_cnd++;
                    id_ctvcb++;
                    if (sl_cb == 2)
                    {
                        ds_ctvcb.Add(new CT_VECHUYENBAY());
                        ds_ctvcb[id_ctvcb].MaCTVe = vechuyenbay.MaVe.Substring(2) + info.MaChuyenBayVe.Substring(2) + info.dsHK[i].MaHK.Substring(2);
                        ds_ctvcb[id_ctvcb].MaVe = vechuyenbay.MaVe;
                        ds_ctvcb[id_ctvcb].MaCB = info.MaChuyenBayVe;
                        ds_ctvcb[id_ctvcb].MaHK = info.dsHK[i].MaHK;
                        ds_ctvcb[id_ctvcb].MaLoaiDV = info.Loaidichvuve;
                        ds_ctvcb[id_ctvcb].ChoNgoi = dscngoiphatsinhve[id_cnv];
                        id_cnv++;
                        id_ctvcb++;
                    }
                    
                }
            }
            #endregion

            #region ThemHKDaiDien
            //Neu nguoi dat ko co trong CSDL
            if(info.Nguoidat.MaHK == null) //Can kiem tra lai
            {
                hkdd.MaHK = "HK" + (c + 1).ToString();
                hkdd.Ho = info.Nguoidat.HoHK;
                hkdd.Ten = info.Nguoidat.TenHK;
                hkdd.CMND = info.Nguoidat.SoCMND.ToString();
                hkdd.DanhTinh = info.Nguoidat.DanhTinh;
                hkdd.Email = info.Nguoidat.Email;
                hkdd.GioiTinh = info.Nguoidat.GioiTinh;
                hkdd.NgaySinh = info.Nguoidat.NgaySinh;
                hkdd.SoDTBan = info.Nguoidat.SDTBan.ToString();
                hkdd.SoDTDiDong = info.Nguoidat.SDTDidong.ToString();
                hkdd.SoFax = info.Nguoidat.SoFax.ToString();
                c++;
                info.Nguoidat.MaHK = hkdd.MaHK;
                db.AddToHANHKHACHes(hkdd);
            }
            else{
                if(!db.HANHKHACHTHANTHIETs.Select(x=>x.MaHK).Contains(info.Nguoidat.MaHK))
                {
                    if(db.HANHKHACHes.Where(x=>x.MaHK == info.Nguoidat.MaHK).Count()!=0)
                    {
                        HANHKHACH thk = db.HANHKHACHes.Single(x => x.MaHK == info.Nguoidat.MaHK);
                        thk.Ho = info.Nguoidat.HoHK;
                        thk.Ten = info.Nguoidat.TenHK;
                        thk.CMND = info.Nguoidat.SoCMND.ToString();
                        thk.DanhTinh = info.Nguoidat.DanhTinh;
                        thk.Email = info.Nguoidat.Email;
                        thk.GioiTinh = info.Nguoidat.GioiTinh;
                        thk.NgaySinh = info.Nguoidat.NgaySinh;
                        thk.SoDTBan = info.Nguoidat.SDTBan.ToString();
                        thk.SoDTDiDong = info.Nguoidat.SDTDidong.ToString();
                        thk.SoFax = info.Nguoidat.SoFax.ToString();
                        info.Nguoidat.MaHK = thk.MaHK;
                    }
                    else
                    {
                        dshk[int.Parse(info.khdd)].DanhTinh = info.Nguoidat.DanhTinh;
                        dshk[int.Parse(info.khdd)].Email = info.Nguoidat.Email;
                        dshk[int.Parse(info.khdd)].GioiTinh = info.Nguoidat.GioiTinh;
                        dshk[int.Parse(info.khdd)].NgaySinh = info.Nguoidat.NgaySinh;
                        dshk[int.Parse(info.khdd)].SoDTBan = info.Nguoidat.SDTBan.ToString();
                        dshk[int.Parse(info.khdd)].SoDTDiDong = info.Nguoidat.SDTDidong.ToString();
                        dshk[int.Parse(info.khdd)].SoFax = info.Nguoidat.SoFax.ToString();
                    }
                }
            }
            vechuyenbay.MaHKDaiDien = info.Nguoidat.MaHK;
            #endregion

            #region Luu vao Database
            db.AddToVECHUYENBAYs(vechuyenbay);
            for (int k = 0; k < id_ctvcb; k++)
                db.AddToCT_VECHUYENBAY(ds_ctvcb[k]);
            //db.SaveChanges();
            #endregion

            #region Cap nhat lai so luong cho ngoi
            string maloaighedi = db.LOAIDICHVUs.Single(p => p.MaLoaiDV == info.Loaidichvudi).MaLoaiGhe;
            string mamaybaydi = db.CHUYENBAYs.Single(p => p.MaCB == info.MaChuyenBayDi).MAYBAY.MaMB;
            string maghembdi = db.GHEMAYBAYs.Single(p => p.MaLoaiGhe == maloaighedi && p.MaMB == mamaybaydi).MaGheMB;
            CT_GHECHUYENBAY ct_gcbdi = db.CT_GHECHUYENBAY.Single(p => p.MaCB == info.MaChuyenBayDi && p.MaGheMB == maghembdi);
            ct_gcbdi.SoGheDaDat += (info.SoNguoiLon + info.SoTreEm);
            CHUYENBAY cbdi = db.CHUYENBAYs.Single(p => p.MaCB == info.MaChuyenBayDi);
            cbdi.TongSoGheDaDat += (info.SoNguoiLon + info.SoTreEm);
            if(sl_cb == 2)
            {
                string maloaigheve = db.LOAIDICHVUs.Single(p => p.MaLoaiDV == info.Loaidichvuve).MaLoaiGhe;
                string mamaybayve = db.CHUYENBAYs.Single(p => p.MaCB == info.MaChuyenBayVe).MAYBAY.MaMB;
                string maghembve = db.GHEMAYBAYs.Single(p => p.MaLoaiGhe == maloaigheve && p.MaMB == mamaybayve).MaGheMB;
                CT_GHECHUYENBAY ct_gcbve = db.CT_GHECHUYENBAY.Single(p => p.MaCB == info.MaChuyenBayVe && p.MaGheMB == maghembve);
                ct_gcbve.SoGheDaDat += (info.SoNguoiLon + info.SoTreEm);
                CHUYENBAY cbve = db.CHUYENBAYs.Single(p => p.MaCB == info.MaChuyenBayVe);
                cbve.TongSoGheDaDat += (info.SoNguoiLon + info.SoTreEm);
            }
            db.SaveChanges();
            #endregion

            return vechuyenbay.MaVe;
        }
        private bool HuyVe(string mave)
        {
            IEnumerable<CT_VECHUYENBAY> ctv = db.CT_VECHUYENBAY.Where(x => x.MaVe == mave);
            foreach (CT_VECHUYENBAY items in ctv)
            {
                string mamaybay = items.CHUYENBAY.MAYBAY.MaMB;
                string maloaighe = items.LOAIDICHVU.LOAIGHE.MaLoaiGhe;
                string maghe = db.GHEMAYBAYs.Single(x => x.MaLoaiGhe == maloaighe && x.MaMB == mamaybay).MaGheMB;
                string macb = items.CHUYENBAY.MaCB;
                items.CHUYENBAY.TongSoGheDaDat -= 1;
                db.CT_GHECHUYENBAY.Single(x => x.MaCB == macb && x.MaGheMB == maghe).SoGheDaDat -= 1;
                db.CT_VECHUYENBAY.DeleteObject(items);
            }
            db.VECHUYENBAYs.DeleteObject(db.VECHUYENBAYs.Single(x => x.MaVe == mave)); 
            return true;
        }
        public bool HamKiemTraHuyVe()
        {
            DateTime check = DateTime.Now.AddHours(-12);
            IEnumerable<string> dsmvhuy = db.VECHUYENBAYs.Where(x => x.NgayDatVe < check && x.MaTrangThaiVe == "002").Select(x=>x.MaVe);
            foreach (string mv in dsmvhuy)
                HuyVe(mv);
            db.SaveChanges();
            return true;
        }

        #endregion

        #region Offline
        public VECHUYENBAY LayVeChuyenBay(string mave)
        {
            IEnumerable<VECHUYENBAY> temp = db.VECHUYENBAYs.Where(x => x.MaVe == mave);
            if (temp.Count() == 0)
                return null;
            else
                return temp.First();
        }

        public IEnumerable<CHUYENBAY> LayDSChuyenBay(string mave)
        {
            DateTime Ngaydi =
                db.CT_VECHUYENBAY.Where(x => x.MaVe == mave).OrderBy(x => x.CHUYENBAY.NgayDi).First().CHUYENBAY.NgayDi.Value;
            Ngaydi = Ngaydi.Date;
            DateTime nextday = Ngaydi.AddDays(1);
            return from p in db.CHUYENBAYs
                   where (p.NgayDi.Value >= Ngaydi && p.NgayDi.Value < nextday
                          && p.CT_GHECHUYENBAY.Sum(o => o.SoGheDaDat) < p.MAYBAY.TongSoGhe &&
                          p.TrangThaiCB.CompareTo("Đặt vé") != 0) 
                   select p;
        }

        public IEnumerable<CHUYENBAY> LayDSCB(string madb, string madv, int sl)
        {
            string maloaighe = db.LOAIDICHVUs.Single(k => k.MaLoaiDV == madv).MaLoaiGhe;
            IEnumerable<CHUYENBAY> rs = db.CHUYENBAYs.Where(x => x.DUONGBAY.MaDuongBay == madb && x.TrangThaiCB.CompareTo("Đặt vé") != 0);
            List<CHUYENBAY> result = new List<CHUYENBAY>();
            foreach (CHUYENBAY chuyenbay in rs)
            {
                if (db.GHEMAYBAYs.Where(x => x.MaLoaiGhe == maloaighe && x.MaMB == chuyenbay.MaMB).Count() != 0)
                {
                    GHEMAYBAY gmb = db.GHEMAYBAYs.Single(x => x.MaLoaiGhe == maloaighe && x.MaMB == chuyenbay.MaMB);
                    if ((chuyenbay.CT_GHECHUYENBAY.Single(x => x.MaGheMB == gmb.MaGheMB && x.MaCB == chuyenbay.MaCB).
                             SoGheDaDat + sl) <= gmb.SoLuong)
                    {
                        result.Add(chuyenbay);
                    }
                }
            }
            return result.AsEnumerable();
        }

        public void CapNhatVe(string maVe, string maCbdi, string maCbve)
        {
            VECHUYENBAY vechuyenbay = db.VECHUYENBAYs.Single(x => x.MaVe == maVe);
            int soluong = (int)vechuyenbay.SoLuong;

            string loaighedi = vechuyenbay.CT_VECHUYENBAY.OrderBy(x => x.CHUYENBAY.NgayDi).First().LOAIDICHVU.MaLoaiGhe;
            CHUYENBAY chuyenbaydi = vechuyenbay.CT_VECHUYENBAY.OrderBy(x => x.CHUYENBAY.NgayDi).First().CHUYENBAY;
            chuyenbaydi.TongSoGheDaDat = chuyenbaydi.TongSoGheDaDat - soluong;
            chuyenbaydi.CT_GHECHUYENBAY.Single(x => x.GHEMAYBAY.MaLoaiGhe == loaighedi).SoGheDaDat -= soluong;


            foreach (CT_VECHUYENBAY ctVechuyenbay in vechuyenbay.CT_VECHUYENBAY.Where(x=>x.MaCB == chuyenbaydi.MaCB))
            {
                ctVechuyenbay.MaCB = maCbdi;
            }
            CHUYENBAY chuyenbaydimoi = db.CHUYENBAYs.Single(x => x.MaCB == maCbdi);
            chuyenbaydimoi.TongSoGheDaDat += soluong;
            chuyenbaydimoi.CT_GHECHUYENBAY.Single(x => x.GHEMAYBAY.MaLoaiGhe == loaighedi).SoGheDaDat += soluong;


            if(vechuyenbay.LoaiVe == "Khu hoi")
            {
                string loaigheve = vechuyenbay.CT_VECHUYENBAY.OrderBy(x => x.CHUYENBAY.NgayDi).Last().LOAIDICHVU.MaLoaiGhe;
                CHUYENBAY chuyenbayve = vechuyenbay.CT_VECHUYENBAY.OrderBy(x => x.CHUYENBAY.NgayDi).Last().CHUYENBAY;
                chuyenbayve.TongSoGheDaDat = chuyenbayve.TongSoGheDaDat - soluong;
                chuyenbayve.CT_GHECHUYENBAY.Single(x => x.GHEMAYBAY.MaLoaiGhe == loaigheve).SoGheDaDat -= soluong;

                foreach (CT_VECHUYENBAY ctVechuyenbay in vechuyenbay.CT_VECHUYENBAY.Where(x => x.MaCB == chuyenbayve.MaCB))
                {
                    ctVechuyenbay.MaCB = maCbve;
                }
                CHUYENBAY chuyenbayvemoi = db.CHUYENBAYs.Single(x => x.MaCB == maCbve);
                chuyenbayvemoi.TongSoGheDaDat += soluong;
                chuyenbayvemoi.CT_GHECHUYENBAY.Single(x => x.GHEMAYBAY.MaLoaiGhe == loaigheve).SoGheDaDat += soluong;
            }
            db.SaveChanges();
        }

        public DICHVU KiemTraDoiNgayBay(string madv)
        {
            DICHVU temp1 = db.DICHVUs.Single(x => x.MaDichVu == "001");
            DICHVU temp2 = db.DICHVUs.Single(x => x.MaDichVu == "007");
            IEnumerable<DICHVU> ldv = db.LOAIDICHVUs.Single(x => x.MaLoaiDV == madv).DICHVUs;
            if (ldv.Contains(temp1))
                return temp1;
            if (ldv.Contains(temp2))
                return temp2;
            return null;
        }

        public bool ThanhToanVe(string mave)
        {
            VECHUYENBAY vcb = db.VECHUYENBAYs.Single(x => x.MaVe == mave);
            vcb.MaTrangThaiVe = "003";
            vcb.NgayThanhToan = DateTime.Now;
            db.SaveChanges();
            return true;
        }

        #endregion

        #region CheckIn
        public VECHUYENBAY GetVeByMaVe(string Ho, string Ten, string MaVe)
        {
            IEnumerable<VECHUYENBAY> CI = from ve in db.VECHUYENBAYs
                                          where (ve.HANHKHACH.Ho == Ho && ve.HANHKHACH.Ten == Ten
                                                 && ve.MaVe == MaVe)
                                          select ve;
            if (CI.Count() > 0)
            {
                VECHUYENBAY temp = new VECHUYENBAY();
                foreach (var CIn in CI)
                {
                    if (CIn.MaTrangThaiVe != "001" && CIn.MaTrangThaiVe != "002")
                    {
                        temp = CIn;
                    }
                }
                return temp;
            }
            else
            {
                return null;
            }
        }
        public CT_VECHUYENBAY GetVeByMaCB(string Ho, string Ten, string MaCB)
        {
            IEnumerable<CT_VECHUYENBAY> CI = from CT in db.CT_VECHUYENBAY
                                             where (CT.VECHUYENBAY.HANHKHACH.Ho == Ho && CT.VECHUYENBAY.HANHKHACH.Ten == Ten
                                              && CT.MaCB == MaCB)
                                             select CT;
            if (CI.Count() > 0)
            {
                CT_VECHUYENBAY temp = new CT_VECHUYENBAY();
                foreach (var CIn in CI)
                {
                    if (CIn.VECHUYENBAY.MaTrangThaiVe != "001" && CIn.VECHUYENBAY.MaTrangThaiVe != "002")
                    {
                        temp = CIn;
                    }
                }
                return temp;
            }
            else
            {
                return null;
            }
        }
        public Boolean CheckStatus(string MaVe)
        {
            VECHUYENBAY Check = (from ve in db.VECHUYENBAYs
                                 where ve.MaVe == MaVe
                                 select ve).First();
            if (Check.MaTrangThaiVe == "004")
                return true;
            else
                return false;
        }
        public void CIn(string MaVe, Boolean Status, string LoaiVe)
        {
            VECHUYENBAY ve = db.VECHUYENBAYs.Single(v => v.MaVe == MaVe);

            if (LoaiVe == "Mot chieu")
            {
                ve.MaTrangThaiVe = "004";
            }
            if (Status == false && LoaiVe == "Khu hoi")
            {
                ve.MaTrangThaiVe = "004";
            }
            if (Status == true && LoaiVe == "Khu hoi")
            {
                ve.MaTrangThaiVe = "005";
            }
            ve.NgayLamThuTuc = DateTime.Now.Date;
            //db.SaveChanges();  

        }
        public IEnumerable<CT_VECHUYENBAY> GetInfo(string Mave, string LoaiVe, Boolean Status, int SoLuong)
        {
            IEnumerable<CT_VECHUYENBAY> GetInfo = from CT in db.CT_VECHUYENBAY
                                                  where CT.MaVe == Mave
                                                  select CT;
            if (LoaiVe == "Mot chieu")
            {
                return GetInfo;
            }
            if (Status == false && LoaiVe == "Khu hoi")
            {
                return GetInfo.Skip(0).Take(SoLuong);
            }
            else
            {
                return GetInfo.Skip(SoLuong);
            }
        }
        public Boolean IsCheck(string TrangThai, string LoaiVe)
        {
            if (TrangThai == "004" && LoaiVe == "Mot chieu")
            {
                return true;
            }
            else if (TrangThai == "005" && LoaiVe == "Khu hoi")
            {
                return true;
            }
            else
                return false;
        }
        public Boolean CheckWhere(string NoiDi, string MaVe)
        {
            IEnumerable<CT_VECHUYENBAY> Where = from CT in db.CT_VECHUYENBAY
                                                where CT.CHUYENBAY.DUONGBAY.SANBAY.MaSB == NoiDi && CT.MaVe == MaVe
                                                select CT;
            if (Where.Count() > 0)
            {
                return true;
            }
            else
                return false;
        }
        public int CheckTime(DateTime NgayDi)
        {
            TimeSpan Time = NgayDi.Subtract(DateTime.Now);
            int Temp = DateTime.Now.Day - NgayDi.Day;
            if (Time.TotalHours <= 0)
            {
                return 0;
            }
            else if (Time.TotalHours <= 40 && Temp == 0)
            {
                return 1;
            }
            else if (Time.TotalHours <= 40 && Temp != 0)
            {
                return 2;
            }
            else
                return 0;
        }
        public Boolean IsRelation(string Mahk)
        {
            IEnumerable<HANHKHACHTHANTHIET> hk = from tt in db.HANHKHACHTHANTHIETs
                                                 where tt.MaHK == Mahk
                                                 select tt;
            if (hk.Count() > 0)
            {
                return true;
            }
            else
                return false;
        }
        public void IncreaseMark(string MaHk, int diem = 1)
        {
            HANHKHACHTHANTHIET hk = db.HANHKHACHTHANTHIETs.Single(k => k.MaHK == MaHk);

            hk.DiemTichLuyHienTai = hk.DiemTichLuyHienTai + diem;
            if (hk.DiemTichLuyHienTai <= 5)
            {
                hk.MaLoaiHK = "TT";
            }
            else if (hk.DiemTichLuyHienTai <= 10)
            {
                hk.MaLoaiHK = "Bac";
            }
            else if (hk.DiemTichLuyHienTai <= 15)
            {
                hk.MaLoaiHK = "Titan";
            }
            else
                hk.MaLoaiHK = "Vang";
            //db.SaveChanges();
        }
        #endregion

        #region Customer
        public IEnumerable<HANHKHACHTHANTHIET> LayDSHKTT()
        {
            return db.HANHKHACHTHANTHIETs.ToList();
        }

        [HttpGet]
        public HANHKHACHTHANTHIET LayHKTT(string mahk)
        {
            return db.HANHKHACHTHANTHIETs.Single(x => x.MaHK == mahk);
        }
        public void CapNhatHK(string mahk, HANHKHACHTHANTHIET hk)
        {
            HANHKHACHTHANTHIET temp = db.HANHKHACHTHANTHIETs.Single(x => x.MaHK == mahk);
            temp.HANHKHACH.DanhTinh = hk.HANHKHACH.DanhTinh;
            temp.HANHKHACH.Ho = hk.HANHKHACH.Ho;
            temp.HANHKHACH.Ten = hk.HANHKHACH.Ten;
            temp.HANHKHACH.GioiTinh = hk.HANHKHACH.GioiTinh;
            temp.HANHKHACH.NgaySinh = hk.HANHKHACH.NgaySinh;
            temp.DiaChi = hk.DiaChi;
            temp.QuocTich = hk.QuocTich;
            temp.HANHKHACH.CMND = hk.HANHKHACH.CMND;
            temp.HANHKHACH.Email = hk.HANHKHACH.Email;
            temp.HANHKHACH.SoDTDiDong = hk.HANHKHACH.SoDTDiDong;
            temp.HANHKHACH.SoDTBan = hk.HANHKHACH.SoDTBan;
            temp.HANHKHACH.SoFax = hk.HANHKHACH.SoFax;
            temp.LoaiTheThanhToan = hk.LoaiTheThanhToan;
            temp.MaSoThe = hk.MaSoThe;
            temp.ChuThe = hk.ChuThe;
            temp.NgayHetHan = hk.NgayHetHan;
            temp.DiemTichLuyHienTai = hk.DiemTichLuyHienTai;
            IncreaseMark(mahk, (int)(hk.DiemTichLuyHienTai - temp.DiemTichLuyHienTai));
            db.SaveChanges();
        }
        public void TaoHK(HANHKHACHTHANTHIET hk)
        {
            if(hk.MaHK == null)
            {
                if (db.HANHKHACHes.Count() == 0)
                {
                    hk.MaHK = "HK1";
                    hk.HANHKHACH.MaHK = "HK1";
                }
                else
                {
                    string mhk = "HK" + (int.Parse(db.HANHKHACHes.AsEnumerable<HANHKHACH>().Last().MaHK.Substring(2)) + 1).ToString();
                    hk.MaHK = mhk;
                    hk.HANHKHACH.MaHK = mhk;
                }
                hk.MaQuyen = "HKTT";
                hk.MaLoaiHK = "TT";
                db.AddToHANHKHACHes(hk.HANHKHACH);
                db.AddToHANHKHACHTHANTHIETs(hk);
                db.SaveChanges();
            }
            else
            {
                HANHKHACHTHANTHIET temp = new HANHKHACHTHANTHIET();
                temp.MaHK = hk.MaHK;
                temp.MaLoaiHK = "TT";
                temp.MaQuyen = "HKTT";
                temp.MatKhau = hk.MatKhau;
                db.AddToHANHKHACHTHANTHIETs(temp);
                db.SaveChanges();
                CapNhatHK(hk.MaHK, hk);
            }
        }
        public void XoaHK(string mahk)
        {
            db.HANHKHACHTHANTHIETs.DeleteObject(db.HANHKHACHTHANTHIETs.Single(x => x.MaHK == mahk));
            db.SaveChanges();
        }

        public IEnumerable<CT_VECHUYENBAY> LayDSCTV(string mahk)
        {
            return db.CT_VECHUYENBAY.Where(x => x.MaHK == mahk).OrderByDescending(x=>x.CHUYENBAY.NgayDi).ToList();
        }

        #endregion

        #region Flight Info
        // Lấy danh sách các thành phố có thể tạo chuyến bay
        public List<SelectListItem> LayDSTP()
        {
            var DSTP = (from p in db.DUONGBAYs
                        where ((p.HangKhaiThac.CompareTo("AIRLINE")) == 0) && p.KichHoat == true
                        orderby p.SANBAY.THANHPHO.QuocGia
                        select new SelectListItem
                        {
                            Value = p.SANBAY.MaSB,
                            Text = p.SANBAY.THANHPHO.TenTP
                        }).Distinct();
            return DSTP.ToList();
        }
        public List<SelectListItem> LayDSTPDen(string id)
        {
            var DSTPDen = (from p in db.DUONGBAYs
                           where (p.SANBAY.MaSB == id && p.HangKhaiThac == "AIRLINE" && p.KichHoat == true)
                           orderby p.SANBAY1.THANHPHO.QuocGia
                           select new SelectListItem
                           {
                               Value = p.SANBAY1.MaSB,
                               Text = p.SANBAY1.THANHPHO.TenTP
                           }).Distinct();
            return DSTPDen.ToList();
        }
        public List<SelectListItem> LayDSTPDi(string id)
        {
            var DSTPDi = (from p in db.DUONGBAYs
                          where (p.SANBAY1.MaSB == id)
                          select new SelectListItem
                          {
                              Value = p.SANBAY.MaSB,
                              Text = p.SANBAY.THANHPHO.TenTP
                          }).Distinct();
            return DSTPDi.ToList();
        }
        // Dựa vào sân bay đến và sân bay đi tìm kiếm loại hành trình của chuyến bay đó
        public List<SelectListItem> LayHanhTrinh(string sbdi, string sbden)
        {
            var hanhtrinh = from p in db.DUONGBAYs
                            where (p.SANBAY.MaSB == sbdi && p.SANBAY1.MaSB == sbden)
                            select new SelectListItem
                            {
                                Text = p.HanhTrinh,
                                Value = p.MaDuongBay
                            };
            return hanhtrinh.ToList();
        }
        // Lấy mã đường bay của 1 chuyến bay thẳng
        public List<SelectListItem> LayMaDB_BayThang(string sbdi, string sbden)
        {
            var MaDb = from p in db.DUONGBAYs
                       where (p.SANBAY.MaSB == sbdi && p.SANBAY1.MaSB == sbden)
                       select new SelectListItem
                       {
                           Value = p.MaDuongBay,
                           Text = p.LoaiDuongBay
                       };
            return MaDb.ToList();
        }
        // Lấy danh sách đường bay nối tiếp của một chuyến bay hợp tác
        public List<SelectListItem> LayMaDB_HopTac(string sbdi, string sbden)
        {
            var MaDb = from p in db.CT_DUONGBAY
                       where (p.DUONGBAY.SANBAY.MaSB == sbdi && p.DUONGBAY.SANBAY1.MaSB == sbden)
                       select new SelectListItem
                       {
                           Value = p.MaDuongBayNoiTiep,
                           Text = p.DUONGBAY.HangKhaiThac
                       };
            return MaDb.ToList();
        }
        public int? LayThoiGianBay(string madb)
        {
            var thoigianbay = (from p in db.DUONGBAYs
                               where (p.MaDuongBay == madb)
                               select p.ThoiGianBay).Single();
            return thoigianbay;
        }
        public List<SelectListItem> LayDSMayBay(string loaidb, DateTime ngaydi, DateTime ngayden)
        {
            var checkcb = (from p in db.CHUYENBAYs
                           where !(ngayden < p.NgayDi || ngaydi > p.NgayDen)
                           select p.MaMB).Distinct();
            List<string> li_checkdb = checkcb.ToList();
            var DSMB = from p in db.MAYBAYs
                       where p.LoaiDuongBay == loaidb
                       && !li_checkdb.Contains(p.MaMB)
                       select new SelectListItem
                       {
                           Value = p.MaMB,
                           Text = p.TenMB
                       };
            return DSMB.ToList();
        }
        // Lấy sân bay đi và sân bay đến dựa vào mã đường bay
        public List<FlightItem> LaySanBay(string madb)
        {
            var sb = (from p in db.CT_DUONGBAY
                      where p.MaDuongBayChinh == madb
                      orderby p.STT
                      select new FlightItem
                      {
                          MaDB = p.MaDuongBayNoiTiep,
                          SbDen = p.DUONGBAY1.SanBayDen,
                          SbDi = p.DUONGBAY1.SanBayDi,
                          HangKhaiThac = p.DUONGBAY1.HangKhaiThac,
                          ThoiGianBay = p.DUONGBAY1.ThoiGianBay,
                          STT = p.STT
                      });
            return sb.ToList();
        }
        public List<CHUYENBAY> ListFlightNow()
        {
            DateTime now = DateTime.Now.Date;
            var allflight = from p in db.CHUYENBAYs
                            where p.NgayDi > now && !p.MaCB.Contains("_")
                            select p;
            return allflight.ToList();
        }
        public List<CHUYENBAY> ListFlightPast()
        {
            DateTime now = DateTime.Now.Date;
            var allflight = from p in db.CHUYENBAYs
                            where p.NgayDi <= now && !p.MaCB.Contains("_")
                            select p;
            return allflight.ToList();
        }
        // Lay thong tin day du cua 1 chuyen bay 
        public FlightItem LayThongTinChuyenBay(CHUYENBAY cb)
        {
            var thongtincb = from p in db.CHUYENBAYs
                             where p.MaCB == cb.MaCB
                             select new FlightItem
                             {
                                 TrangThaiCB = p.TrangThaiCB,
                                 MaDB = p.MaDuongBay,
                                 MaCB = p.MaCB,
                                 SbDi = p.DUONGBAY.SanBayDi,
                                 SbDen = p.DUONGBAY.SanBayDen,
                                 HangKhaiThac = p.DUONGBAY.HangKhaiThac,
                                 TongSoGheDaDat = p.TongSoGheDaDat,
                                 ThoiGianBay = p.DUONGBAY.ThoiGianBay,
                                 STT = 0,
                                 Maybay = 0,
                                 NgayDi = (DateTime)p.NgayDi,
                                 NgayDen = p.NgayDen,
                                 KhuyenMai = p.KhuyenMai,
                                 NgayKhoiTao = (DateTime)p.NgayKhoiTao,
                                 MaMB = p.MaMB
                             };
            return thongtincb.First();
        }
        public List<FlightItem> LayThongTinChuyenBay(CHUYENBAY cb, int dif)
        {
            var thongtincb = from p in db.CHUYENBAYs
                             where p.MaCB.Contains(cb.MaCB) && p.MaCB.Contains("_")
                             select new FlightItem
                             {
                                 TrangThaiCB = p.TrangThaiCB,
                                 MaDB = p.MaDuongBay,
                                 MaCB = p.MaCB,
                                 SbDi = p.DUONGBAY.SanBayDi,
                                 SbDen = p.DUONGBAY.SanBayDen,
                                 HangKhaiThac = p.DUONGBAY.HangKhaiThac,
                                 TongSoGheDaDat = p.TongSoGheDaDat,
                                 ThoiGianBay = p.DUONGBAY.ThoiGianBay,
                                 STT = 0,
                                 KhuyenMai = p.KhuyenMai,
                                 NgayKhoiTao = (DateTime)p.NgayKhoiTao,
                                 Maybay = 0,
                                 NgayDi = (DateTime)p.NgayDi,
                                 NgayDen = (DateTime)p.NgayDen,
                                 MaMB = p.MaMB
                             };
            return thongtincb.ToList();
        }

        #region Tạo chuyến bay
        // Tạo chuyến bay noi dia
        public bool CreateFlight(FlightItem flight, string mamb, int loaicb, int khuyenmai)
        {
            int num;
            CHUYENBAY cb = new CHUYENBAY();
            cb.MaDuongBay = flight.MaDB;
            cb.TongSoGheDaDat = 0;
            cb.NgayDi = flight.NgayDi;
            cb.NgayDen = flight.NgayDen;
            cb.TrangThaiCB = "Đặt vé";
            cb.NgayKhoiTao = DateTime.Now.Date;
            cb.KhuyenMai = khuyenmai;
            var temp = from p in db.CHUYENBAYs
                       where p.MaCB.Contains("VN")
                       select p.MaCB;
            List<string> li = temp.ToList();
            if (temp.Count() == 0)
            {
                num = 1;
            }
            else
            {
                num = int.Parse(li.Last().Substring(2)) + 1;
            }
            cb.MaCB = "VN" + num.ToString();

            if (flight.Maybay == 1)
            {
                cb.MaMB = mamb;
            }
            // lấy lên thông tin về ghế của máy bay và insert vào bảng CT_Ghế chuyến bay
            var ghemaybays = from p in db.GHEMAYBAYs
                             where p.MaMB == cb.MaMB
                             select p.MaGheMB;
            List<string> li_ghemaybay = ghemaybays.ToList();

            try
            {
                db.AddToCHUYENBAYs(cb);
                db.SaveChanges();
                foreach (var ghe in li_ghemaybay)
                {
                    CT_GHECHUYENBAY ghecb = new CT_GHECHUYENBAY { MaGheMB = ghe, MaCB = cb.MaCB, SoGheDaDat = 0 };
                    db.AddToCT_GHECHUYENBAY(ghecb);
                    db.SaveChanges();
                }
            }
            catch (System.Exception ex)
            {
                return false;

            }
            return true;
        }
        // tao chuyen bay nhieu chang, tao chuyen bay tu A->D
        public string CreateFlight(DateTime ngaydi, string madb, int khuyenmai)
        {
            int num;
            CHUYENBAY cb = new CHUYENBAY();
            cb.MaDuongBay = madb;
            cb.NgayDi = ngaydi;
            cb.TrangThaiCB = "Đặt vé";
            cb.NgayKhoiTao = DateTime.Now.Date;
            cb.KhuyenMai = khuyenmai;
            var temp = from p in db.CHUYENBAYs
                       where p.MaCB.Contains("QT") && !p.MaCB.Contains("_")
                       select p.MaCB;
            List<string> li = temp.ToList();
            if (temp.Count() == 0)
            {
                num = 1;
            }
            else
            {
                num = int.Parse(li.Last().Substring(2)) + 1;
            }
            cb.MaCB = "QT" + num.ToString();

            try
            {
                db.AddToCHUYENBAYs(cb);
                db.SaveChanges();
            }
            catch (System.Exception ex)
            {
                return "false";
            }
            return cb.MaCB;
        }
        // Tao cac chuyen bay trung gian cua chuyen bay nhieu chang co ma la macb
        public bool CreateFlight(FlightItem flight, string mamb, int loaicb, string macb, int stt)
        {
            if (macb == "false") return false;
            CHUYENBAY cb = new CHUYENBAY();
            cb.MaCB = macb + "_" + stt.ToString();
            cb.MaDuongBay = flight.MaDB;
            cb.TongSoGheDaDat = 0;
            cb.NgayDi = flight.NgayDi;
            cb.NgayDen = flight.NgayDen;
            cb.TongSoGheDaDat = 0;
            cb.TrangThaiCB = "Đặt vé";
            cb.NgayKhoiTao = DateTime.Now.Date;
            if (flight.Maybay == 1)
            {
                cb.MaMB = mamb;

            }
            try
            {
                db.AddToCHUYENBAYs(cb);
                db.SaveChanges();
                if (flight.Maybay == 1)
                {
                    var ghemaybays = from p in db.GHEMAYBAYs
                                     where p.MaMB == cb.MaMB
                                     select p.MaGheMB;
                    List<string> li_ghemaybay = ghemaybays.ToList();
                    foreach (var ghe in li_ghemaybay)
                    {
                        CT_GHECHUYENBAY ghecb = new CT_GHECHUYENBAY { MaGheMB = ghe, MaCB = cb.MaCB, SoGheDaDat = 0 };
                        db.AddToCT_GHECHUYENBAY(ghecb);
                        db.SaveChanges();
                    }
                }
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region Cập nhật log của việc thay đổ
        public bool CapNhatChuyenBayLog(string trangthaichuyenbay, string manv, string macb, string lydo, DateTime ngaychinhsua)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.TrangThaiChuyenBay = trangthaichuyenbay;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }

        }
        public bool CapNhatChuyenBayLog(string mamb, string manv, string macb, string lydo, DateTime ngaychinhsua, int dif = 1)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.MaMB = mamb;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
        public bool CapNhatChuyenBayLog(DateTime ngaydi, string manv, string macb, string lydo, DateTime ngaychinhsua)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.NgayDi = ngaydi;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
        public bool CapNhatChuyenBayLog(string trangthaichuyenbay, string mamb, DateTime ngaydi, string manv, string macb, string lydo, DateTime ngaychinhsua)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.NgayDi = ngaydi;
            log.TrangThaiChuyenBay = trangthaichuyenbay;
            log.MaMB = mamb;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
        public bool CapNhatChuyenBayLog(string trangthaichuyenbay, DateTime ngaydi, string manv, string macb, string lydo, DateTime ngaychinhsua)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.NgayDi = ngaydi;
            log.TrangThaiChuyenBay = trangthaichuyenbay;

            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
        public bool CapNhatChuyenBayLog(string mamb, DateTime ngaydi, string manv, string macb, string lydo, DateTime ngaychinhsua, int dif = 1)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.NgayDi = ngaydi;
            log.MaMB = mamb;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
        public bool CapNhatChuyenBayLog(string mamb, string trangthaichuyenbay, string manv, string macb, string lydo, DateTime ngaychinhsua)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.TrangThaiChuyenBay = trangthaichuyenbay;
            log.MaMB = mamb;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

        // Check all va cap nhat
        public bool CapNhatChuyenBayLog(int? khuyenmai, string trangthaichuyenbay, string mamb, DateTime? ngaydi,string manv, string macb, string lydo, DateTime ngaychinhsua)
        {
            CHUYENBAY_LOG log = new CHUYENBAY_LOG();
            log.NgayDi = ngaydi;
            log.TrangThaiChuyenBay = trangthaichuyenbay;
            log.MaMB = mamb;
            log.MaNV = manv;
            log.MaCB = macb;
            log.LyDoThayDoi = lydo;
            log.KhuyenMai = khuyenmai;
            log.NgayChinhSua = ngaychinhsua;
            try
            {
                db.AddToCHUYENBAY_LOG(log);
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
        #endregion
        public bool KiemTraMB(string mamb, DateTime dtdi, DateTime dtden)
        {
            var checkcb = (from p in db.CHUYENBAYs
                           where !(dtden < p.NgayDi || dtdi > p.NgayDen)
                           select p.MaMB).Distinct();
            List<string> li_checkdb = checkcb.ToList();
            if (li_checkdb.Contains(mamb)) return false;
            else return true;
        }

        public bool CapNhatChuyenBay(FlightItem fitem)
        {
            var cb = db.CHUYENBAYs.Single(p => p.MaCB == fitem.MaCB);
            cb.MaMB = fitem.MaMB;
            cb.NgayDi = fitem.NgayDi;
            cb.NgayDen = fitem.NgayDen;
            cb.TrangThaiCB = fitem.TrangThaiCB;
            cb.KhuyenMai = fitem.KhuyenMai;
            try
            {
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }

        }
        #endregion

        #region Router
        public List<DUONGBAY> DSDuongBay()
        {
            var dsdb = from p in db.DUONGBAYs
                       where (p.HangKhaiThac.CompareTo("AIRLINE") == 0)
                       select p;
            return dsdb.ToList();
        }
        public bool CapNhatDuongBay(DUONGBAY dbay)
        {
            var _dbay = db.DUONGBAYs.Single(p => p.MaDuongBay == dbay.MaDuongBay);
            _dbay.ThoiGianBay = dbay.ThoiGianBay;
            _dbay.GiaCoBan = dbay.GiaCoBan;
            _dbay.KichHoat = dbay.KichHoat;
            try
            {
                db.SaveChanges();
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }

        public List<DUONGBAY> DSDuongBayKhac()
        {
            var dsdb = from p in db.DUONGBAYs
                       where (p.HangKhaiThac.CompareTo("AIRLINE") != 0)
                       select p;
            return dsdb.ToList();
        }

        public List<SelectListItem> LayDsSanBay()
        {
            var dssb = from p in db.SANBAYs
                       select new SelectListItem
                       {
                           Value = p.MaSB,
                           Text = p.THANHPHO.TenTP
                       };
            return dssb.ToList();
        }
        #endregion
    }
}